[Developers.IO 2019 Sapporoこぼれ話] Excelのテーブル機能と構造化参照

データ分析

#Microsoft Excel

じょんすみす

2019.10.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、小澤です。

先日、Developers.IO 2019 Sapporoにて「全部Excelだけで実現しようとして後悔するデータ分析」という発表をしました。実は、このなかでちらっと触れたけど、詳しくは語っていないこぼれ話があるので今回はその話について書いていきます。

Excelのテーブル機能と構造化参照とは

テーブルとは

Excelにはテーブルというものを作成する機能があります。

一見、通常の表を作ったのと同じようなものになっていますが、ただの表になっているものからテーブルを作成することで享受できるメリットがあります。

テーブルを作成するには挿入 > テーブルとするとかCtrl-Tで実行します。

対象となる範囲を選択を選択するダイアログが表示されます。この際、あらかじめ範囲を選択しておけば、その範囲が適用された状態となります。

たったこれだけです。試しにやってみると見た目は変わりますが他に何が異なるのかわかりませんね。

このテーブルは、決まった構造でデータを入れておくことにより、それに特化した操作が可能になるというものになっています。なので、操作しないことには変化がわかりません。

では、まず「決まった構造」というのがどういうものなのか見ていきましょう。これは、以下のようなものとなっており、SQLのテーブルやR, PythonのData Frameと同じような構造となります。

例えば、合計列が「数量*単価」で計算可能なように列に対して、計算結果を入れておくのは問題ありませんが、途中の行に小計などは含めてはいけません(これをやるのはピボットテーブルの役目です)。また、一つの列内に複数のデータを入れるのもNGです。上記のデータのように項目ごとに行を分けます。また、上記のデータに対して、例えば「同一購入者であっても複数回購入があった場合はそれらを区別したい」ということがあるかと思います。そういった場合は、購入日時列や同一購入であることを表すトランザクションID列を付与するなどします。

また、テーブルには名前を付けることが可能です。テーブルの一部のセルが選択されている状態でテーブルデザイン > テーブル名から設定が可能です。

デフォルトでは、作った順に「テーブル1」「テーブル2」のような名前になっています。この値は1つのExcelファイル内で重複がNG以外任意の名前を付けれます。データ内容に応じた適切な名前を付けると見通しが良くなるでしょう。

さて、このテーブルでどういうメリットがあるかですが、主に以下のようなものが挙げられます。

データが変わったときに範囲を自動で判断してくれる
スライサーを利用できる
構造化参照が利用できる

前者は例えば、セッション発表中に紹介したPower Queryを使ってる時にデータを更新するとそれに合わせてテーブルの行数も自動で調整されます。

そして、何らかの計算を行ってるときに行数が変わっても自動で範囲を判定してくれるのが構造化参照となります。

構造化参照とは

さて、構造化参照ですがテーブルの性質上、必ず列名があり、列の中には同一種類のデータが入っている状態になります。この性質を利用して、 A1:A10 のようなセルの範囲の代わりに「購入者列」のような指定が可能になるものになっています。

例えば、先ほどのデータであれば総売り上げは =SUM(F1:F8) のように計算していたものを =SUM(Orders[合計]) のように計算できます。 Ordersはテーブル名です。データの行数が変わっても合計列の範囲を指定しなおす必要はありません。

また、テーブル名や列名はキーボードから入力する際に補完も可能です。そのため、対象範囲の指定ミスや対象のタイプミスも防ぎやすくなります。

この構造化参照にはいくつかの種類があります。これはテーブル内のどの項目であるかを選択できるものとなっています。

ヘッダの情報取得は =Orders[[#見出し]購入者] のように指定します。

これだけでは、特に意味はなさそうですね。ちなみに、 =Orders[#見出し] のみだとすべての列名が配列で返ってきます。 TRANSPOSE関数と組み合わせることで、以下のように列一覧情報を取得することも可能です。

続いて、データ内容を取得する方法を見てみます。これは、先ほどSUM関数の利用でも見た通り Orders[合計] のように選択可能な他、 Orders[[#データ], [合計]] のようにデータ部分を明示したうえで、列を選択することも可能です。また、隣接している範囲であれば、Orders[数量]:[合計] のように「:」で範囲を指定することも可能です。

また、データ部分には「この行」という機能もあります。今回のデータの合計行は元々持っているわけではなく、数量と単価から計算しています。通常であれば、 =D2 * E2 のように計算してそれを末尾までフィルするかもしれませんがテーブルであれば列名の前に「@」を付けることで現在の行の値を得ることが可能です。

1件目のデータで入力すると以降は特に何もしなくても自動で入ってくれます。また、すべての行で全く同じものが入力されているのも確認できるかと思います。これは、「この行」での指定がExcelの行番号をみて取ってきてくれるため、同じ式であっても計算している場所に応じて適切な値を選択しているためです。

なお、行番号さえおなじであればテーブル外や別シートでもそのまま取得可能ですが、あくまでもテーブル内での位置ではなく行番号の絶対値での指定となりますのでご注意ください。

構造化参照の応用

最後に簡単な応用例を見てみましょう。今回は先ほどのものに加えて以下のテーブルもあります。

さて、この2つのテーブルがある状態でやりたいことと言えば... そう！VLOOKUPです！

VLOOKUPにもテーブルが利用できます。使い方は対象範囲に =VLOOKUP([@購入品ID], Items[#データ], 2, FALSE) のようにデータ全体や、 =VLOOKUP([@購入品ID], Items, 2, FALSE) のようにテーブルそのものを指定します。

なお、VLOOKUPの仕様で対象となる列は番号で指定するため、列名での指定はできません。どうしてもやりたい場合は、MATCH関数を利用して =VLOOKUP([@購入品ID], Items, MATCH("Price", Items[#見出し]), FALSE) のように列名から対象となる番号を指定します。

構造化参照で柔軟な範囲指定はできないの？

さて、このように便利な構造化参照ですが、指定できる範囲はここで解説したようにあらかじめ用意された項目のみとなっています。そのため、例えば「10行目から30行目が欲しいんだ！」のような指定はこのやり方ではできません(先ほどVLOOKUPにMATCH関数を組み合わせたように頑張ればなんとかなりますがｗ)。もちろんテーブルもExcelの中に存在しているので無視して、 A10:F30 のように書いていしまうことは可能です。

このように細かく柔軟なことには対応していない構造化参照ですが、できないことがメリットであるともいえます。構造化参照を使っている範囲内では処理対象としている範囲が明確になりますし、一般的なデータ分析の範囲で用意されているような使い方に絞れるというわけです。これによって、「この指定ができないってことは何か特殊な使い方をしようとしてるんじゃないか？」と思わせるきっかけも与えてくれます。

もちろん、すべての場合においてこのやり方以外禁止とすべきとは限りませんが、何をしているかわかりやすいExcel作りに役立つことは間違いないでしょう。